﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ET.Monitor.DAL
{
    public class ChartDAL
    {
        object objJudge = null;
        int intCount = 0;
        string strSql = "", strErrMsg = "";
        DataTable dtTable = new DataTable();
        DataSet dsChart = new DataSet();

        InvokeWebService iw = new InvokeWebService();
        string rtdbRelationAddress = "";
        string rtdbRealTimeAddress = "";
        string rtdbNameSpace = "";

        public ChartDAL()
        {
            rtdbRelationAddress = ConfigurationManager.ConnectionStrings["RelationURL"].ConnectionString;
            rtdbRealTimeAddress = ConfigurationManager.ConnectionStrings["RealTimeURL"].ConnectionString;
            rtdbNameSpace = ConfigurationManager.ConnectionStrings["NameSpace"].ConnectionString;
        }

        #region
        /// <summary>
        /// 根据USERID从T_INFO_CHART_USERTEMPLATE获取模板ID，说明。现用于TendManage页面DataGrid呈现
        /// </summary>
        /// <param name="user_id">用户ID</param>
        /// <returns></returns>
        public DataTable Get_Chartid(string uid)
        {
            strSql = "select row_number()over(order by T_CHARTID )as id,T_CHARTID,T_CHARTDESC from administrator.T_INFO_CHART_USERTEMPLATE where T_USERID = '" + uid + "'";
            dtTable = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dtTable;
            //dtTable = db.RunDataTable(strSql, out strErrMsg);
            //return dtTable;
        }
        #endregion

        #region 获取等级
        public DataTable GetLevel(int grad, string level)
        {
            DataTable dt = new DataTable();
            if (grad == 1)
                strSql = "select distinct(T_LEVEL1) from T_BASE_CHARTPARAID";
            else if (grad == 2)
            {
                if (level != "")
                    strSql = "select distinct(T_LEVEL2) from T_BASE_CHARTPARAID where T_LEVEL1='" + level + "'";
                else
                    strSql = "select distinct(T_LEVEL2) from T_BASE_CHARTPARAID";
            }
            else
            {
                if (level != "")
                    strSql = "select distinct(T_LEVEL3) from T_BASE_CHARTPARAID where T_LEVEL2='" + level + "'";
                else
                    strSql = "select distinct(T_LEVEL3) from T_BASE_CHARTPARAID";
            }
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 自定义趋势

        #region 获取趋势模板
        public DataTable GetModel(string uid)
        {
            strSql = "select row_number()over(order by T_CHARTID )as id,T_CHARTID,T_CHARTDESC from T_INFO_CHART_USERTEMPLATE where T_USERID = '" + uid + "'";
            dtTable = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dtTable;
        }
        #endregion

        #region 获取趋势模板总数
        public int GetModelCount(string uid)
        {
            strSql = "select count(*) from T_INFO_CHART_USERTEMPLATE where T_USERID='" + uid + "'";
            intCount = (int)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunRowCount", new object[] { strSql, strErrMsg });
            return intCount;
        }
        #endregion

        #region 趋势模板分页
        public DataTable GetModelPaging(int count, int pageIndex, string uid)
        {
            strSql = "select top " + count + " row_number()over(order by T_CHARTID )as id,T_CHARTID,T_CHARTDESC,I_MULITYAXIS,D_YMAX,D_YMIN,I_YAUTO from T_INFO_CHART_USERTEMPLATE where (T_CHARTID not in(select top " + count * (pageIndex - 1) + " T_CHARTID from T_INFO_CHART_USERTEMPLATE order by ID_KEY asc)) and T_USERID='" + uid + "' order by ID_KEY asc";
            dtTable = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dtTable;
        }
        #endregion

        #region 获取趋势测点信息
        public DataTable GetLinePointInfo(string modelId)
        {
            strSql = "";
            //strSql = "select c.D_PERIOD,c.D_YMAX,c.D_YMIN,c.I_YAUTO,c.T_UNIT,p.T_PARAID,p.T_PARADESC_EN,p.T_PARADESC_CN,p.T_PARATYPE,";
            strSql = "select c.D_PERIOD,c.D_YMAX,c.D_YMIN,c.I_YAUTO,c.T_UNIT,c.I_FLAG,c.T_paRADESC,c.T_PARAID,p.T_PARADESC_EN,p.T_PARADESC_CN,p.T_PARATYPE,";
            //strSql += "  p.T_REALTIME,p.T_SQL from T_INFO_CHART_CHARTPARA c inner join T_BASE_CHARTPARAID p";
            strSql += "  p.T_REALTIME,p.T_SQL,c.T_CHARTID from T_INFO_CHART_CHARTPARA c left join T_BASE_CHARTPARAID p";
            strSql += "  on c.T_PARAID=p.T_PARAID where c.T_CHARTID='" + modelId + "'";
            dtTable = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dtTable;
        }

        #endregion

        #region Edit Foumula
        public bool EditFormula(string cid, string title, string formula, string oTitle)
        {
            strSql = "update T_INFO_CHART_USERTEMPLATE set T_CHARTDESC ='" + title + "' where T_USERID='admin' and T_CHARTID='" + cid + "'";
            bool u = (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
            strSql = " update T_INFO_CHART_CHARTPARA set T_PARAID='" + formula + "',T_PARADESC='" + title + "' where T_CHARTID='" + cid + "' and T_PARADESC = '" + oTitle + "'";
            bool m = (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
            if (u && m)
                return true;
            else
                return false;
        }
        #endregion

        #region 获取模板编号最大值
        public int GetModelMax()
        {
            strSql = "select isnull(MAX(ID_KEY),1) from T_INFO_CHART_USERTEMPLATE";
            intCount = (int)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunRowCount", new object[] { strSql, strErrMsg });
            return intCount;
        }
        #endregion

        #region 添加模板
        public bool AddModel(string id, string userid, string y, int max, int min, int auto, string desc)
        {
            strSql = "insert into T_INFO_CHART_USERTEMPLATE values('" + id + "','" + userid + "'," + y + "," + max + "," + min + "," + auto

+ ",'" + desc + "');";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 添加模板测点信息
        public bool AddModelPoint(string pid, string mid, string pl, string pyl, int max, int min, int auto, string unit, string checkModel,

string formula)
        {
            strSql = "insert into T_INFO_CHART_CHARTPARA values('" + mid + "','" + pid + "','" + pl + "','" + pyl + "'," + max + "," + min +

"," + auto + ",'" + unit + "','" + checkModel + "','" + formula + "');";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 删除模板
        public bool delModel(string id)
        {
            strSql = "  delete from T_INFO_CHART_CHARTPARA where T_CHARTID='" + id + "'; delete from T_INFO_CHART_USERTEMPLATE where T_CHARTID = '" + id + "'; ";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #endregion

        //public string GetMongdbString(string tag)
        //{
        //    return dl.GetRealTimeValueWithOpen(tag).ToString();
        //}

        private DataTable GetPointInfo(string points)
        {
            strSql = "select T_PARATYPE,T_SQL,T_REALTIME from T_BASE_CHARTPARAID where T_REALTIME in(" + points + ")";
            dtTable = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dtTable;
        }

        public IList<Hashtable> GetLine(string[] point, string[] name, DateTime st, DateTime et, int seconds)
        {
            //string points = "";
            //string lName = "";

            //for (int i = 0; i < point.Length; i++)
            //{
            //    points += "'" + point[i] + "',";
            //}
            //points = points.Substring(0, points.Length - 1);

            //DataTable dtPointInfo = GetPointInfo(points);
            ///*获取测点信息*/

            //string[] realPoint = null;
            //string realTime = "";
            //string sqlPoint = "";
            //for (int i = 0; i < dtPointInfo.Rows.Count; i++)
            //{
            //    if (dtPointInfo.Rows[i]["T_PARATYPE"].ToString() == "RT")
            //        realTime += dtPointInfo.Rows[i]["T_REALTIME"] + ",";
            //    else
            //        sqlPoint += dtPointInfo.Rows[i]["T_SQL"] + ",";
            //}

            //IList<Hashtable> list = new List<Hashtable>();

            //#region 获取实时库数据

            //if (realTime != "")
            //{
            //    realTime = realTime.Substring(0, realTime.Length - 1);
            //    realPoint = realTime.Split(',');
            //    point = realPoint;

            //    for (int i = 0; i < point.Length; i++)
            //    {
            //        Hashtable ht = new Hashtable();
            //        ht.Add("name", name[i]);
            //        ht.Add("yAxis", i);

            //        ArrayList alList = new ArrayList();
            //        ArrayList ltList = new ArrayList();
            //        DateTime dtNow = st;
            //        DateTime _sTime = new DateTime(1970, 1, 1);

            //        dtTable = dl.GetHisTableWithOpen(point[i], st, et);

            //        Hashtable htdt = new Hashtable();

            //        int k = 0;
            //        while (dtNow < et)
            //        {
            //            ArrayList listdt = new ArrayList();
            //            alList = new ArrayList();

            //            string timeStamp = DateTimeToUTC(dtNow).ToString();
            //            DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
            //            long lTime = long.Parse(timeStamp + "0000000");
            //            TimeSpan toNow = new TimeSpan(lTime);
            //            DateTime dtResult = dtStart.Add(toNow);
            //            alList.Add(Convert.ToInt64((dtResult - _sTime).TotalMilliseconds.ToString()));

            //            DataRow[] dr = dtTable.Select("Time <='" + dtNow + "'", "Time desc");
            //            if (dr.Length > 0)
            //            {
            //                alList.Add(GetDouble(Convert.ToDouble(dr[0][2]), 2));
            //            }
            //            else
            //            {
            //                dr = dtTable.Select("1=1", "Time asc");
            //                alList.Add(GetDouble(Convert.ToDouble(dr[0][2]), 2));
            //            }

            //            ltList.Add(alList);

            //            if (k != 0)
            //                dtNow = dtNow.AddSeconds(seconds);
            //            k++;
            //        }
            //        ht.Add("data", ltList);
            //        list.Add(ht);
            //    }
            //}
            //#endregion

            ////if (sqlPoint != "")
            ////{
            ////    sqlPoint = sqlPoint.Substring(0, sqlPoint.Length - 1);


            //sqlPoint = "station-dl";
            //sqlPoint = "1865";
            //string[] tsql = sqlPoint.Split(',');
            //for (int i = 0; i < tsql.Length; i++)
            //{
            //    if (tsql[i] == "station-dl")
            //    {
            //        StationDAL sd = new StationDAL();

            //        double[] v = sd.GetStationChartDl(tsql[i], Convert.ToDateTime("2014-12-24 0:00:00"), Convert.ToDateTime("2014-12-24 23:59:59"), seconds);
            //        Hashtable ht = new Hashtable();
            //        ht.Add("name", "电量");

            //    }
            //}
            ////}

            IList<Hashtable> list = new List<Hashtable>();
            for (int i = 0; i < point.Length; i++)
            {
                Hashtable ht = new Hashtable();


                ArrayList alList = new ArrayList();
                ArrayList ltList = new ArrayList();
                DateTime dtNow = st;
                DateTime _sTime = new DateTime(1970, 1, 1);

                double[] val = (double[])iw.RunWebService(rtdbRealTimeAddress, rtdbNameSpace, "GetHistValueByStep", new object[] { point[i], st, et, seconds });


                if (val != null)
                {
                    ht.Add("name", name[i]);
                    ht.Add("yAxis", i);
                    Hashtable htdt = new Hashtable();

                    int k = 0;
                    while (dtNow < et)
                    {
                        if (k == val.Length)
                            break;
                        ArrayList listdt = new ArrayList();
                        alList = new ArrayList();

                        string timeStamp = DateTimeToUTC(dtNow).ToString();
                        DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1));
                        long lTime = long.Parse(timeStamp + "0000000");
                        TimeSpan toNow = new TimeSpan(lTime);
                        DateTime dtResult = dtStart.Add(toNow);

                        alList.Add(Convert.ToInt64((dtResult - _sTime).TotalMilliseconds.ToString()));

                        alList.Add(GetDouble(val[k], 2));
                        ltList.Add(alList);

                        if (k != 0)
                            dtNow = dtNow.AddSeconds(seconds);
                        k++;
                    }
                    ht.Add("data", ltList);
                    list.Add(ht);
                }
            }

            return list;
        }

        #region UTC
        public static int DateTimeToUTC(DateTime DT)
        {
            long a = new DateTime(1970, 1, 1, 0, 0, 0, 0).Ticks;
            int rtnInt = 0;
            rtnInt = (int)((DT.Ticks - 8 * 3600 * 1e7 - a) / 1e7);
            return rtnInt;
        }
        #endregion

        #region 四舍五入
        /// <summary>
        /// 四舍五入
        /// </summary>
        /// <param name="result">要转换的数值</param>
        /// <param name="num">保留位数</param>
        /// <returns></returns>
        public double GetDouble(double result, int num)
        {
            if (!double.IsNaN(result))
            {
                string res = result.ToString();
                string results = "";
                int index = res.IndexOf('.');

                if (res.Length - index == num + 1)
                    return Convert.ToDouble(res);
                else
                {
                    if (index > 0)
                    {
                        index += num;
                        res = res + "000000000000000000";
                        res = res.Remove(0, index + 1);
                        results = result + "000000000000000000";
                        results = results.ToString().Substring(0, index + 1);
                        res = res.Substring(0, 1);

                        string point = "0.";

                        for (int count = 0; count < num - 1; count++)
                        {
                            point += "0";
                        }
                        point += "1";


                        if (Convert.ToInt32(res) > 4)
                        {
                            results = (Convert.ToDouble(results) + Convert.ToDouble(point)).ToString();
                            res = results;
                        }
                        else
                        {
                            res = results;
                        }
                    }
                    else
                    {
                        res += ".";
                        for (int i = 0; i < num; i++)
                        {
                            res += "0";
                        }
                    }
                    return Convert.ToDouble(res);
                }
            }
            else
                return result;
        }
        #endregion
    }
    public class ModelConvertHelper<T> where T : new()  // 此处一定要加上new()
    {

        public static IList<T> ConvertToModel(DataTable dt)
        {

            IList<T> ts = new List<T>();// 定义集合
            Type type = typeof(T); // 获得此模型的类型
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;
                    if (dt.Columns.Contains(tempName))
                    {
                        if (!pi.CanWrite) continue;
                        object value = dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }
    }
}
